﻿
<?php
require_once('./functions.php');
require_once('./Database.class.php');
$db = new Database();

// Get the array of classificateur|mesure elements 
$aC = getElementsFrom('c_');
$aM = getElementsFrom('m_');
?>

<html>
<head>
	<title>Projet EDD</title>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<link href="./css/style.css" rel="stylesheet" type="text/css" />
</head>
<body>
<h1>Projet EDD</h1>
<?php
/*print "<pre>";
print_r($aC);
print "</pre>";

print "<pre>";
print_r($aM);
print "</pre>";*/

if(count($aM) == 0){echo "<div class = 'warning'>Fatal error: You must specify a mesure</div>";exit;}

if( count($aC) == 0 ){
	// Select
	/*print "<pre>";
	print_r(getTableAndElementName($aM));
	print "</pre>";*/
	
	$a = getTableAndElementName($aM);

	foreach($a as $tableName => $elementList){
		
		foreach($elementList as $k => $eName){
			$tableAndElement = getStringBetweenTwoDelimiters($eName,'(',')');
			$tableAndElement = explode('.',$tableAndElement);// only 2 elements
			$elementName = $tableAndElement[1];
			echo '<div class = "olap">Q = < , '.$aM[$elementName]['path'].', '.$aM[$elementName]['operator'].'></div><br />';
		}
		
		$sql = 'Select '.implode(',',$elementList).' From '.$tableName;
		echo '<div class = "sql">'.$sql.'</div><br />';
		
		$resource = $db->requeteSQL($sql);
		echo '<div class="result"><table><tr>';
			for($i = 0; $i < count($elementList); $i++){
				echo '<th>'.mysql_field_name($resource, $i).'</th>';
			}
			echo '</tr>';
		
		while($aRes = mysql_fetch_assoc($resource)){
			echo '<tr>';
			foreach($aRes as $key => $value){
				echo '<td>'.$value.'</td>';
			}
			echo '</tr>';				
		}				
		echo '</table></div><br />';		
	}
}
else{// with the presence of classificateur
	
	/*-------------------Build the OLAP presentation---------------------------*/
	// Studied case: Date,City,Category
	$classificateurElement = '';	
	foreach($aC as $elementName => $elementInfo){
		$tempClassElement[] = $elementInfo['path'];
	}
	
	$classificateurElement = implode(' &#94;',array_values($tempClassElement));
	
	echo '<div class = "olap">';
	$mesureElement = '';
	$mesureOperator= '';	
	if(count($aM) == 1){
		foreach($aM as $elementName => $elementInfo){
			$mesureElement = $elementInfo['path'];
			$mesureOperator = $elementInfo['operator'];
		}
		echo 'Q = ( <'.$classificateurElement.', '.$mesureElement.', '.$mesureOperator.' > )<br />';
	}
	else{
		foreach($aM as $elementName => $elementInfo){
			$mesureElement = $elementInfo['path'];
			$mesureOperator = $elementInfo['operator'];
			$temp[] = '('.$mesureElement.','.$mesureOperator.')';
		}
		
		echo 'Q = ( <'.$classificateurElement.', '.implode(',',$temp).' > )<br />';
	}
	echo '</div>';
	/*--------------------------- Build de SQL query---------------------------*/	
	// Build Select from classificateur elements
	$tempSelectElement = array();
	$tempSelectTable = array();
	// Check if object is chosen
	if(array_key_exists('o',$aC) == true){
		$tempSelectElement = getSelectElementWhenOChosen($aM);		
		$tempSelectTable = getSelectTableWhenOChosen($tempSelectElement);
		
		// Remove 'o' from $aC so that we don't have a warning with 'getTableElementRestrictionValues'
		unset($aC['o']);
	}
	else{
		$a = getTableAndElementName($aC, true);
		foreach($a as $t => $elementList){
			foreach($elementList as $k => $e){
				$tempSelectElement[] = $t.'.'.$e;	
			}		
			$tempSelectTable[] = $t; 
		}
	}
	// Create the Group By clause
	$groupBy = implode(',',array_values($tempSelectElement));
	
	// Build Select from mesure elements	
	$b = getTableAndElementName($aM,true);
	foreach($b as $t => $elementList){
		foreach($elementList as $k => $e){
			$tempSelectElement[] = $e;
			// Check if it's neccesary to add a new table to From clause
			$tableFromMesureElement = getStringBetweenTwoDelimiters($e,'(','.');
			if(in_array($tableFromMesureElement,$tempSelectTable) == false){
				$tempSelectTable[] = $tableFromMesureElement;
			}			
		}
	}
	
	// Insert 'TF' if it doesn't exist in all the tables
	if(in_array('TF',$tempSelectTable) == false){array_unshift($tempSelectTable,'TF');}
	
	$SQLElement = implode(',',array_values($tempSelectElement));
	$SQLTable = implode(',',array_values($tempSelectTable));
	
	// Get the WHERE clause
	$tempWhere = array();	
	foreach($tempSelectElement as $key => $tableAndElement){
		$temp = getWhereClause($tableAndElement);
		if( ($temp != null) && (in_array($temp,$tempWhere) == false) ){
			$tempWhere[] = $temp;
		}
	}
	
	// Get the restriction values
	$c = getTableElementRestrictionValues($aC);
	
	foreach($c as $tableElementName => $restrictionValueList){
		$r = array();
		foreach($restrictionValueList as $key => $restrictionValue){
			$r[] = $tableElementName." like '".$restrictionValue."'";
		}
		if(count($r) > 0){
			if(count($r) > 1){
				$tempWhere[] = '('.implode(' OR ',$r).')';
			}
			else{ $tempWhere[] = $r[0];} 
		}
	}

	
	$whereClause = (count($tempWhere) > 0) ? implode(' AND ',$tempWhere) : '';
	$whereClause = ($whereClause == '') ? '' : ' Where '.$whereClause;
	
	// Get the Having clause
	$tempHaving = array();
	foreach($aM as $elementName => $elementInfo){
		$havingOperator = $elementInfo['havingOperator'];
		if( ($havingOperator != null) && ($havingOperator != '0') ){
			$tableName = ( count($elementInfo['tables']) > 1 ) ? $elementInfo['tables'][1] : $elementInfo['tables'][0]; // Priority TF
			$tempHaving[] = $elementInfo['operator'].'('.$tableName.'.'.$elementName.') '.$havingOperator.' '.$elementInfo['havingValue'];
		}		
	}
	
	$havingClause = (count($tempHaving) > 0) ? implode(' AND ',$tempHaving) : '';
	$havingClause = ($havingClause == '') ? '' : ' Having ('.$havingClause.')';
	
	$limitClause = ' Limit 0,30';
	
	/*--------------------------- Show the result -----------------------------*/
	// Prepare the SQL query to show and to execute
	$sql = 'Select '.$SQLElement.
			' From '.$SQLTable.
			$whereClause.
			' Group by '.$groupBy.
			$havingClause.
			$limitClause;
	
	$whereClause2 = ($whereClause == '') ? $whereClause : $whereClause.'<br />';
	$havingClause2 = ($havingClause == '') ? '' : ' Having ('.$havingClause.')<br />';
	$sql2 = 'Select '.$SQLElement.' <br />'.
			' From '.$SQLTable.'<br />'.
			$whereClause2.
			' Group by '.$groupBy.'<br />'.
			$havingClause.
			$limitClause;
	echo '<br /><div class = "sql">';
	echo $sql2; // Show the query
	echo '</div><br />';
	
	$resource = $db->requeteSQL($sql);
	
	// Show the result
	if(!$resource){echo "<div class = 'warning'>QUERY ERROR: <strong>$sql</strong></div>";exit;}
	if($db->getNombreLignes() == 0){
		echo "<div class = 'warning'>Sorry, there isn't any result for this query</div>";
		exit;
	}
	
	echo '<div class="result"><table><tr>';
		for($i = 0; $i < count($tempSelectElement); $i++){
			echo '<th>'.mysql_field_name($resource, $i).'</th>';
		}
		echo '</tr>';
	
	while($aRes = mysql_fetch_assoc($resource)){
		echo '<tr>';
		foreach($aRes as $key => $value){
			echo '<td>'.$value.'</td>';
		}
		echo '</tr>';				
	}				
	echo '</table></div><br />';		
	
}
/*echo "<br /><br />--------POST------------";
print "<pre>";
print_r($_POST);
print "</pre>";*/
?>
<div id="footer">
	Projet développé par Kim Toan PHAM, Ibrahim ZEHHAF, Liang HONG
</div>
</body>
</html>